﻿using System;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json.Linq;
using Utilities;

namespace DataCollector
{
    public class Database
    {
        public static void InsertAchievements(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            var characterId = (string) jsonParser.SelectToken("characterId");
            var completedAchievements = (string) jsonParser.SelectToken("completedAchievements");
            var completedTimeStamps = (string) jsonParser.SelectToken("completedTimeStamps");

            var sqlCommand = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection, CommandText = "InsertCharacterAchievements"};
            sqlCommand.Parameters.Clear();
            sqlCommand.CommandTimeout = 60;
            sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
            sqlCommand.Parameters.AddWithValue("AchievementsCompleted", completedAchievements);
            sqlCommand.Parameters.AddWithValue("AchievementsCompletedTimestamp", completedTimeStamps);
            sqlCommand.ExecuteScalar();
        }

        public static void InsertAppearance(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            int characterId = int.Parse(jsonParser.SelectToken("characterId").ToString());
            int faceVariation = int.Parse(jsonParser.SelectToken("faceVariation").ToString());
            int featureVariation = int.Parse(jsonParser.SelectToken("featureVariation").ToString());
            int hairColor = int.Parse(jsonParser.SelectToken("hairColor").ToString());
            int hairVariation = int.Parse(jsonParser.SelectToken("hairVariation").ToString());
            bool showCloak = bool.Parse(jsonParser.SelectToken("showCloak").ToString());
            bool showHelm = bool.Parse(jsonParser.SelectToken("showHelm").ToString());
            int skinColor = int.Parse(jsonParser.SelectToken("skinColor").ToString());

            var sqlCommand = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection, CommandText = "InsertCharacterAppearance"};
            sqlCommand.Parameters.Clear();
            sqlCommand.CommandTimeout = 60;
            sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
            sqlCommand.Parameters.AddWithValue("FaceVariation", faceVariation);
            sqlCommand.Parameters.AddWithValue("FeatureVariation", featureVariation);
            sqlCommand.Parameters.AddWithValue("HairColor", hairColor);
            sqlCommand.Parameters.AddWithValue("HairVariation", hairVariation);
            sqlCommand.Parameters.AddWithValue("ShowCloak", showCloak);
            sqlCommand.Parameters.AddWithValue("ShowHelm", showHelm);
            sqlCommand.Parameters.AddWithValue("SkinColor", skinColor);
            sqlCommand.ExecuteScalar();
        }

        public static void InsertCompanions(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            var characterId = (string) jsonParser.SelectToken("characterId");
            var companionsAsString = (string) jsonParser.SelectToken("companionsAsString");

            var sqlCommand = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection, CommandText = "InsertCharacterCompanions"};
            sqlCommand.Parameters.Clear();
            sqlCommand.CommandTimeout = 60;
            sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
            sqlCommand.Parameters.AddWithValue("Companions", companionsAsString);
            sqlCommand.ExecuteScalar();
        }

        public static void InsertItems(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            InsertItem(jsonParser, "back", sqlConnection);
            InsertItem(jsonParser, "chest", sqlConnection);
            InsertItem(jsonParser, "feet", sqlConnection);
            InsertItem(jsonParser, "finger1", sqlConnection);
            InsertItem(jsonParser, "finger2", sqlConnection);
            InsertItem(jsonParser, "hands", sqlConnection);
            InsertItem(jsonParser, "head", sqlConnection);
            InsertItem(jsonParser, "legs", sqlConnection);
            InsertItem(jsonParser, "mainHand", sqlConnection);
            InsertItem(jsonParser, "neck", sqlConnection);
            InsertItem(jsonParser, "offHand", sqlConnection);
            InsertItem(jsonParser, "ranged", sqlConnection);
            InsertItem(jsonParser, "shirt", sqlConnection);
            InsertItem(jsonParser, "shoulder", sqlConnection);
            InsertItem(jsonParser, "tabard", sqlConnection);
            InsertItem(jsonParser, "trinket1", sqlConnection);
            InsertItem(jsonParser, "trinket2", sqlConnection);
            InsertItem(jsonParser, "waist", sqlConnection);
            InsertItem(jsonParser, "wrist", sqlConnection);
        }

        public static void InsertItemEnhancements(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));
            var characterId = (string) jsonParser.SelectToken("characterId");

            InsertItemEnhancement(characterId, jsonParser, "back", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "chest", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "feet", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "finger1", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "finger2", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "hands", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "head", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "legs", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "mainHand", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "neck", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "offHand", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "ranged", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "shirt", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "shoulder", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "tabard", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "trinket1", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "trinket2", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "waist", sqlConnection);
            InsertItemEnhancement(characterId, jsonParser, "wrist", sqlConnection);
        }

        public static void InsertEquipment(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            var characterId = (string) jsonParser.SelectToken("characterId");
            var averageItemLevel = (string) jsonParser.SelectToken("averageItemLevel");
            var averageItemLevelEquipped = (string) jsonParser.SelectToken("averageItemLevelEquipped");
            var backItemId = (string) jsonParser.SelectToken("backItemId");
            var chestItemId = (string) jsonParser.SelectToken("chestItemId");
            var feetItemId = (string) jsonParser.SelectToken("feetItemId");
            var finger1ItemId = (string) jsonParser.SelectToken("finger1ItemId");
            var finger2ItemId = (string) jsonParser.SelectToken("finger2ItemId");
            var handsItemId = (string) jsonParser.SelectToken("handsItemId");
            var headItemId = (string) jsonParser.SelectToken("headItemId");
            var legsItemId = (string) jsonParser.SelectToken("legsItemId");
            var mainHandItemId = (string) jsonParser.SelectToken("mainHandItemId");
            var neckItemId = (string) jsonParser.SelectToken("neckItemId");
            var offHandItemId = (string) jsonParser.SelectToken("offHandItemId");
            var rangedItemId = (string) jsonParser.SelectToken("rangedItemId");
            var shirtItemId = (string) jsonParser.SelectToken("shirtItemId");
            var shoulderItemId = (string) jsonParser.SelectToken("shoulderItemId");
            var tabardItemId = (string) jsonParser.SelectToken("tabardItemId");
            var trinket1ItemId = (string) jsonParser.SelectToken("trinket1ItemId");
            var trinket2ItemId = (string) jsonParser.SelectToken("trinket2ItemId");
            var waistItemId = (string) jsonParser.SelectToken("waistItemId");
            var wristItemId = (string) jsonParser.SelectToken("wristItemId");

            var sqlCommand = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection, CommandText = "InsertCharacterEquipment"};
            sqlCommand.Parameters.Clear();
            sqlCommand.CommandTimeout = 60;
            sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
            sqlCommand.Parameters.AddWithValue("AverageItemLevel", averageItemLevel);
            sqlCommand.Parameters.AddWithValue("AverageItemLevelEquipped", averageItemLevelEquipped);
            sqlCommand.Parameters.AddWithValue("Back", (string.IsNullOrEmpty(backItemId) ? "" : backItemId));
            sqlCommand.Parameters.AddWithValue("Chest", (string.IsNullOrEmpty(chestItemId) ? "" : chestItemId));
            sqlCommand.Parameters.AddWithValue("Feet", (string.IsNullOrEmpty(feetItemId) ? "" : feetItemId));
            sqlCommand.Parameters.AddWithValue("Finger1", (string.IsNullOrEmpty(finger1ItemId) ? "" : finger1ItemId));
            sqlCommand.Parameters.AddWithValue("Finger2", (string.IsNullOrEmpty(finger2ItemId) ? "" : finger2ItemId));
            sqlCommand.Parameters.AddWithValue("Hands", (string.IsNullOrEmpty(handsItemId) ? "" : handsItemId));
            sqlCommand.Parameters.AddWithValue("Head", (string.IsNullOrEmpty(headItemId) ? "" : headItemId));
            sqlCommand.Parameters.AddWithValue("Legs", (string.IsNullOrEmpty(legsItemId) ? "" : legsItemId));
            sqlCommand.Parameters.AddWithValue("MainHand", (string.IsNullOrEmpty(mainHandItemId) ? "" : mainHandItemId));
            sqlCommand.Parameters.AddWithValue("Neck", (string.IsNullOrEmpty(neckItemId) ? "" : neckItemId));
            sqlCommand.Parameters.AddWithValue("OffHand", (string.IsNullOrEmpty(offHandItemId) ? "" : offHandItemId));
            sqlCommand.Parameters.AddWithValue("Ranged", (string.IsNullOrEmpty(rangedItemId) ? "" : rangedItemId));
            sqlCommand.Parameters.AddWithValue("Shirt", (string.IsNullOrEmpty(shirtItemId) ? "" : shirtItemId));
            sqlCommand.Parameters.AddWithValue("Shoulder", (string.IsNullOrEmpty(shoulderItemId) ? "" : shoulderItemId));
            sqlCommand.Parameters.AddWithValue("Tabard", (string.IsNullOrEmpty(tabardItemId) ? "" : tabardItemId));
            sqlCommand.Parameters.AddWithValue("Trinket1", (string.IsNullOrEmpty(trinket1ItemId) ? "" : trinket1ItemId));
            sqlCommand.Parameters.AddWithValue("Trinket2", (string.IsNullOrEmpty(trinket2ItemId) ? "" : trinket2ItemId));
            sqlCommand.Parameters.AddWithValue("Waist", (string.IsNullOrEmpty(waistItemId) ? "" : waistItemId));
            sqlCommand.Parameters.AddWithValue("Wrist", (string.IsNullOrEmpty(wristItemId) ? "" : wristItemId));
            sqlCommand.ExecuteScalar();
        }

        public static void InsertMounts(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            var characterId = (string) jsonParser.SelectToken("characterId");
            var mounts = (string) jsonParser.SelectToken("mounts");

            var sqlCommand = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection, CommandText = "InsertCharacterMounts"};
            sqlCommand.Parameters.Clear();
            sqlCommand.CommandTimeout = 60;
            sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
            sqlCommand.Parameters.AddWithValue("Mounts", mounts);
            sqlCommand.ExecuteScalar();
        }

        public static void InsertPets(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            var characterId = (string) jsonParser.SelectToken("characterId");
            int numberOfPets = int.Parse(jsonParser.SelectToken("numberOfPets").ToString());

            for (int i = 0; i < numberOfPets; i++)
            {
                var petId = (string) jsonParser.SelectToken("pets[" + i + "].petId");
                var talentBuild = (string) jsonParser.SelectToken("pets[" + i + "].talentBuild");
                var petName = (string) jsonParser.SelectToken("pets[" + i + "].name");

                var sqlCommand = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection, CommandText = "InsertCharacterPets"};
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandTimeout = 60;
                sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
                sqlCommand.Parameters.AddWithValue("PetId", petId);
                sqlCommand.Parameters.AddWithValue("TalentBuild", talentBuild);
                sqlCommand.Parameters.AddWithValue("Name", petName);
                sqlCommand.ExecuteScalar();
            }
        }

        public static void DeleteProfessions(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            var characterId = (string) jsonParser.SelectToken("characterId");

            var sqlCommand = new SqlCommand {Connection = sqlConnection, CommandText = "DELETE FROM Character_Profession WHERE CharacterId='" + characterId + "'"};
            sqlCommand.ExecuteNonQuery();
        }

        public static void InsertProfessions(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            var characterId = (string) jsonParser.SelectToken("characterId");
            int numberOfProfessions = int.Parse(jsonParser.SelectToken("numberOfProfessions").ToString());

            for (int i = 0; i < numberOfProfessions; i++)
            {
                var name = (string) jsonParser.SelectToken("professions[" + i + "].name");
                var rank = (string) jsonParser.SelectToken("professions[" + i + "].rank");
                var max = (string) jsonParser.SelectToken("professions[" + i + "].max");
                var recipies = (string) jsonParser.SelectToken("professions[" + i + "].recipies");

                var sqlCommand = new SqlCommand {Connection = sqlConnection, CommandType = CommandType.StoredProcedure, CommandText = "InsertCharacter_Profession",};
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandTimeout = 60;
                sqlCommand.Parameters.AddWithValue("Profession", name);
                sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
                sqlCommand.Parameters.AddWithValue("Rank", rank);
                sqlCommand.Parameters.AddWithValue("Max", max);
                sqlCommand.Parameters.AddWithValue("Recipes", recipies);
                sqlCommand.ExecuteScalar();
            }
        }

        public static void InsertRaidInstances(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            int numberOfInstances = int.Parse(jsonParser.SelectToken("numberOfInstances").ToString());

            for (int i = 0; i < numberOfInstances; i++)
            {
                var instanceId = (string) jsonParser.SelectToken("instances[" + i + "].instanceId");
                var instanceName = (string) jsonParser.SelectToken("instances[" + i + "].name");

                var sqlCommand = new SqlCommand {Connection = sqlConnection, CommandType = CommandType.StoredProcedure, CommandText = "InsertRaidInstance"};
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandTimeout = 60;
                sqlCommand.Parameters.AddWithValue("InstanceId", instanceId);
                sqlCommand.Parameters.AddWithValue("Name", instanceName);
                sqlCommand.ExecuteScalar();
            }
        }

        public static void InsertProgression(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            int numberOfBosses = int.Parse(jsonParser.SelectToken("numberOfBosses").ToString());
            var characterId = (string) jsonParser.SelectToken("characterId");

            for (int i = 0; i < numberOfBosses; i++)
            {
                var raidBossId = (string) jsonParser.SelectToken("bosses[" + i + "].raidBossId");
                var raidBossName = (string) jsonParser.SelectToken("bosses[" + i + "].raidBossName");
                var instanceId = (string) jsonParser.SelectToken("bosses[" + i + "].instanceId");
                var normalKills = (string) jsonParser.SelectToken("bosses[" + i + "].normalKills");
                var heroicKills = (string) jsonParser.SelectToken("bosses[" + i + "].heroicKills");

                var sqlCommand = new SqlCommand {Connection = sqlConnection, CommandType = CommandType.StoredProcedure, CommandText = "InsertRaidBoss"};
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandTimeout = 60;
                sqlCommand.Parameters.AddWithValue("RaidBossId", raidBossId);
                sqlCommand.Parameters.AddWithValue("Name", raidBossName);
                sqlCommand.Parameters.AddWithValue("InstanceId", instanceId);
                sqlCommand.ExecuteScalar();

                sqlCommand = new SqlCommand {Connection = sqlConnection, CommandType = CommandType.StoredProcedure, CommandText = "InsertCharacter_Progression"};
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandTimeout = 60;
                sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
                sqlCommand.Parameters.AddWithValue("BossId", raidBossId);
                sqlCommand.Parameters.AddWithValue("NormalKills", normalKills);
                sqlCommand.Parameters.AddWithValue("HeroicKills", heroicKills);
                sqlCommand.ExecuteScalar();
            }
        }

        public static void InsertBattlegrounds(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));
            var characterId = (string) jsonParser.SelectToken("characterId");
            int numberOfBattlegrounds = int.Parse(jsonParser.SelectToken("numberOfBattlegrounds").ToString());

            for (int i = 0; i < numberOfBattlegrounds; i++)
            {
                var battlegroundName = (string) jsonParser.SelectToken("battlegrounds[" + i + "].battlegroundName");
                var played = (string) jsonParser.SelectToken("battlegrounds[" + i + "].played");
                var won = (string) jsonParser.SelectToken("battlegrounds[" + i + "].won");

                var sqlCommand = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection, CommandText = "InsertCharacterBattleground"};
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandTimeout = 60;
                sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
                sqlCommand.Parameters.AddWithValue("BattlegroundName", battlegroundName);
                sqlCommand.Parameters.AddWithValue("Played", played);
                sqlCommand.Parameters.AddWithValue("Won", won);
                sqlCommand.ExecuteScalar();
            }
        }

        public static void DeleteArenaTeams(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            var characterId = (string) jsonParser.SelectToken("characterId");

            var sqlDeleteCommand = new SqlCommand {Connection = sqlConnection, CommandType = CommandType.Text, CommandText = "DELETE FROM Character_ArenaTeams WHERE CharacterId='" + characterId + "'"};
            sqlDeleteCommand.ExecuteNonQuery();
        }

        public static void InsertArenaTeams(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            var characterId = (string) jsonParser.SelectToken("characterId");
            int numberOfArenaTeams = int.Parse(jsonParser.SelectToken("numberOfArenaTeams").ToString());

            for (int i = 0; i < numberOfArenaTeams; i++)
            {
                var arenaTeamName = (string) jsonParser.SelectToken("arenaTeams[" + i + "].arenaTeamName");
                var arenaTeamSize = (string) jsonParser.SelectToken("arenaTeams[" + i + "].arenaTeamSize");
                var arenaTeamRating = (string) jsonParser.SelectToken("arenaTeams[" + i + "].arenaTeamRating");
                var realmId = (string) jsonParser.SelectToken("arenaTeams[" + i + "].realmId");
                var arenaTeamPersonalRating = (string) jsonParser.SelectToken("arenaTeams[" + i + "].arenaTeamPersonalRating");

                var sqlCommand = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection, CommandText = "InsertArenaTeam"};
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandTimeout = 60;
                sqlCommand.Parameters.AddWithValue("Name", arenaTeamName);
                sqlCommand.Parameters.AddWithValue("Size", arenaTeamSize);
                sqlCommand.Parameters.AddWithValue("Rating", arenaTeamRating);
                sqlCommand.Parameters.AddWithValue("RealmId", realmId);
                string arenaId = sqlCommand.ExecuteScalar().ToString();

                sqlCommand.CommandText = "InsertCharacter_ArenaTeam";
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandTimeout = 60;
                sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
                sqlCommand.Parameters.AddWithValue("ArenaId", arenaId);
                sqlCommand.Parameters.AddWithValue("Rating", arenaTeamPersonalRating);
                sqlCommand.ExecuteScalar();
            }
        }

        public static void InsertQuests(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            var characterId = (string) jsonParser.SelectToken("characterId");
            var completedQuests = (string) jsonParser.SelectToken("completedQuests");

            var sqlCommand = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection, CommandText = "InsertCharacterQuests"};
            sqlCommand.Parameters.Clear();
            sqlCommand.CommandTimeout = 60;
            sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
            sqlCommand.Parameters.AddWithValue("QuestsCompleted", completedQuests);
            sqlCommand.ExecuteScalar();
        }

        public static void InsertReputations(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            int characterId = int.Parse(jsonParser.SelectToken("characterId").ToString());
            int numberOfReputations = int.Parse(jsonParser.SelectToken("numberOfReputations").ToString());

            for (int i = 0; i < numberOfReputations; i++)
            {
                int factionId = int.Parse(jsonParser.SelectToken("reputations[" + i + "].factionId").ToString());
                var factionName = (string) jsonParser.SelectToken("reputations[" + i + "].factionName");
                var standing = (string) jsonParser.SelectToken("reputations[" + i + "].standing");
                int value = int.Parse(jsonParser.SelectToken("reputations[" + i + "].value").ToString());
                int max = int.Parse(jsonParser.SelectToken("reputations[" + i + "].max").ToString());

                var sqlCommand = new SqlCommand {Connection = sqlConnection, CommandType = CommandType.StoredProcedure, CommandText = "InsertFaction"};
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandTimeout = 60;
                sqlCommand.Parameters.AddWithValue("FactionId", factionId);
                sqlCommand.Parameters.AddWithValue("Name", factionName);
                sqlCommand.ExecuteScalar();

                sqlCommand = new SqlCommand {Connection = sqlConnection, CommandType = CommandType.StoredProcedure, CommandText = "InsertCharacter_Reputation"};
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandTimeout = 60;
                sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
                sqlCommand.Parameters.AddWithValue("FactionId", factionId);
                sqlCommand.Parameters.AddWithValue("Standing", standing);
                sqlCommand.Parameters.AddWithValue("Value", value);
                sqlCommand.Parameters.AddWithValue("Max", max);
                sqlCommand.ExecuteScalar();
            }
        }

        public static void InsertStats(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            var characterId = (string) jsonParser.SelectToken("characterId");
            int health = int.Parse(jsonParser.SelectToken("health").ToString());
            var powerType = (string) jsonParser.SelectToken("powerType");
            int power = int.Parse(jsonParser.SelectToken("power").ToString());
            int strength = int.Parse(jsonParser.SelectToken("strength").ToString());
            var agility = (string) jsonParser.SelectToken("agility");
            int intellect = int.Parse(jsonParser.SelectToken("intellect").ToString());
            int spirit = int.Parse(jsonParser.SelectToken("spirit").ToString());
            int attackPower = int.Parse(jsonParser.SelectToken("attackPower").ToString());
            int rangedAttackPower = int.Parse(jsonParser.SelectToken("rangedAttackPower").ToString());
            float mastery = float.Parse(jsonParser.SelectToken("mastery").ToString());
            int masteryRating = int.Parse(jsonParser.SelectToken("masteryRating").ToString());
            float criticalPct = float.Parse(jsonParser.SelectToken("criticalPct").ToString());
            int criticalRating = int.Parse(jsonParser.SelectToken("criticalRating").ToString());
            float hitPct = float.Parse(jsonParser.SelectToken("hitPct").ToString());
            int hitRating = int.Parse(jsonParser.SelectToken("hitRating").ToString());
            int hasteRating = int.Parse(jsonParser.SelectToken("hasteRating").ToString());
            int expertiseRating = int.Parse(jsonParser.SelectToken("expertiseRating").ToString());
            int spellPower = int.Parse(jsonParser.SelectToken("spellPower").ToString());
            int spellPenetration = int.Parse(jsonParser.SelectToken("spellPenetration").ToString());
            float spellCriticalPct = float.Parse(jsonParser.SelectToken("spellCriticalPct").ToString());
            int spellCriticalRating = int.Parse(jsonParser.SelectToken("spellCriticalRating").ToString());
            float spellHitPct = float.Parse(jsonParser.SelectToken("spellHitPct").ToString());
            int spellHitRating = int.Parse(jsonParser.SelectToken("spellHitRating").ToString());
            int mp5 = int.Parse(jsonParser.SelectToken("mp5").ToString());
            int mp5Combat = int.Parse(jsonParser.SelectToken("mp5Combat").ToString());
            int armor = int.Parse(jsonParser.SelectToken("armor").ToString());
            float dodgePct = float.Parse(jsonParser.SelectToken("dodgePct").ToString());
            int dodgeRating = int.Parse(jsonParser.SelectToken("dodgeRating").ToString());
            float parryPct = float.Parse(jsonParser.SelectToken("parryPct").ToString());
            int parryRating = int.Parse(jsonParser.SelectToken("parryRating").ToString());
            float blockPct = float.Parse(jsonParser.SelectToken("blockPct").ToString());
            int blockRating = int.Parse(jsonParser.SelectToken("blockRating").ToString());
            int resillience = int.Parse(jsonParser.SelectToken("resillience").ToString());
            int mainHandDmgMin = int.Parse(jsonParser.SelectToken("mainHandDmgMin").ToString());
            int mainHandDmgMax = int.Parse(jsonParser.SelectToken("mainHandDmgMax").ToString());
            float mainHandSpeed = float.Parse(jsonParser.SelectToken("mainHandSpeed").ToString());
            float mainHandDps = float.Parse(jsonParser.SelectToken("mainHandDps").ToString());
            int mainHandExpertise = int.Parse(jsonParser.SelectToken("mainHandExpertise").ToString());
            int offHandDmgMin = int.Parse(jsonParser.SelectToken("offHandDmgMin").ToString());
            int offHandDmgMax = int.Parse(jsonParser.SelectToken("offHandDmgMax").ToString());
            float offHandSpeed = float.Parse(jsonParser.SelectToken("offHandSpeed").ToString());
            float offHandDps = float.Parse(jsonParser.SelectToken("offHandDps").ToString());
            int offHandExpertise = int.Parse(jsonParser.SelectToken("offHandExpertise").ToString());
            int rangedDmgMin = int.Parse(jsonParser.SelectToken("rangedDmgMin").ToString());
            int rangedDmgMax = int.Parse(jsonParser.SelectToken("rangedDmgMax").ToString());
            float rangedSpeed = float.Parse(jsonParser.SelectToken("rangedSpeed").ToString());
            float rangedDps = float.Parse(jsonParser.SelectToken("rangedDps").ToString());
            float rangedCritPct = float.Parse(jsonParser.SelectToken("rangedCritPct").ToString());
            int rangedCritRating = int.Parse(jsonParser.SelectToken("rangedCritRating").ToString());
            float rangedHitPct = float.Parse(jsonParser.SelectToken("rangedHitPct").ToString());
            int rangedHitRating = int.Parse(jsonParser.SelectToken("rangedHitRating").ToString());
            int stamina = int.Parse(jsonParser.SelectToken("stamina").ToString());

            var sqlCommand = new SqlCommand {Connection = sqlConnection, CommandType = CommandType.StoredProcedure, CommandText = "InsertCharacterStats"};
            sqlCommand.Parameters.Clear();
            sqlCommand.CommandTimeout = 60;
            sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
            sqlCommand.Parameters.AddWithValue("Health", health);
            sqlCommand.Parameters.AddWithValue("PowerType", powerType);
            sqlCommand.Parameters.AddWithValue("Power", power);
            sqlCommand.Parameters.AddWithValue("Strength", strength);
            sqlCommand.Parameters.AddWithValue("Agility", agility);
            sqlCommand.Parameters.AddWithValue("Intellect", intellect);
            sqlCommand.Parameters.AddWithValue("Spirit", spirit);
            sqlCommand.Parameters.AddWithValue("AttackPower", attackPower);
            sqlCommand.Parameters.AddWithValue("RangedAttackPower", rangedAttackPower);
            sqlCommand.Parameters.AddWithValue("Mastery", mastery);
            sqlCommand.Parameters.AddWithValue("MasteryRating", masteryRating);
            sqlCommand.Parameters.AddWithValue("CriticalPct", criticalPct);
            sqlCommand.Parameters.AddWithValue("CriticalRating", criticalRating);
            sqlCommand.Parameters.AddWithValue("HitPct", hitPct);
            sqlCommand.Parameters.AddWithValue("HitRating", hitRating);
            sqlCommand.Parameters.AddWithValue("HasteRating", hasteRating);
            sqlCommand.Parameters.AddWithValue("ExpertiseRating", expertiseRating);
            sqlCommand.Parameters.AddWithValue("SpellPower", spellPower);
            sqlCommand.Parameters.AddWithValue("SpellPenetration", spellPenetration);
            sqlCommand.Parameters.AddWithValue("SpellCriticalPct", spellCriticalPct);
            sqlCommand.Parameters.AddWithValue("SpellCriticalRating", spellCriticalRating);
            sqlCommand.Parameters.AddWithValue("SpellHitPct", spellHitPct);
            sqlCommand.Parameters.AddWithValue("SpellHitRating", spellHitRating);
            sqlCommand.Parameters.AddWithValue("MP5", mp5);
            sqlCommand.Parameters.AddWithValue("MP5Combat", mp5Combat);
            sqlCommand.Parameters.AddWithValue("Armor", armor);
            sqlCommand.Parameters.AddWithValue("DodgePct", dodgePct);
            sqlCommand.Parameters.AddWithValue("DodgeRating", dodgeRating);
            sqlCommand.Parameters.AddWithValue("ParryPct", parryPct);
            sqlCommand.Parameters.AddWithValue("ParryRating", parryRating);
            sqlCommand.Parameters.AddWithValue("BlockPct", blockPct);
            sqlCommand.Parameters.AddWithValue("BlockRating", blockRating);
            sqlCommand.Parameters.AddWithValue("Resillience", resillience);
            sqlCommand.Parameters.AddWithValue("MainHandDmgMin", mainHandDmgMin);
            sqlCommand.Parameters.AddWithValue("MainHandDmgMax", mainHandDmgMax);
            sqlCommand.Parameters.AddWithValue("MainHandSpeed", mainHandSpeed);
            sqlCommand.Parameters.AddWithValue("MainHandDps", mainHandDps);
            sqlCommand.Parameters.AddWithValue("MainHandExpertise", mainHandExpertise);
            sqlCommand.Parameters.AddWithValue("OffHandDmgMin", offHandDmgMin);
            sqlCommand.Parameters.AddWithValue("OffHandDmgMax", offHandDmgMax);
            sqlCommand.Parameters.AddWithValue("OffHandSpeed", offHandSpeed);
            sqlCommand.Parameters.AddWithValue("OffHandDps", offHandDps);
            sqlCommand.Parameters.AddWithValue("OffHandExpertise", offHandExpertise);
            sqlCommand.Parameters.AddWithValue("RangedDmgMin", rangedDmgMin);
            sqlCommand.Parameters.AddWithValue("RangedDmgMax", rangedDmgMax);
            sqlCommand.Parameters.AddWithValue("RangedSpeed", rangedSpeed);
            sqlCommand.Parameters.AddWithValue("RangedDps", rangedDps);
            sqlCommand.Parameters.AddWithValue("RangedCritPct", rangedCritPct);
            sqlCommand.Parameters.AddWithValue("RangedCritRating", rangedCritRating);
            sqlCommand.Parameters.AddWithValue("RangedHitPct", rangedHitPct);
            sqlCommand.Parameters.AddWithValue("RangedHitRating", rangedHitRating);
            sqlCommand.Parameters.AddWithValue("Stamina", stamina);
            sqlCommand.ExecuteScalar();
        }

        public static void InsertGlyphs(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));
            int numberOfGlyphs = int.Parse(jsonParser.SelectToken("numberOfGlyphs").ToString());

            for (int i = 0; i < numberOfGlyphs; i++)
            {
                var glyphId = (string) jsonParser.SelectToken("glyphs[" + i + "].glyphId");
                var itemId = (string) jsonParser.SelectToken("glyphs[" + i + "].itemId");
                var icon = (string) jsonParser.SelectToken("glyphs[" + i + "].icon");
                var name = (string) jsonParser.SelectToken("glyphs[" + i + "].name");

                var sqlCommand = new SqlCommand {Connection = sqlConnection, CommandType = CommandType.StoredProcedure, CommandText = "InsertGlyph"};
                sqlCommand.Parameters.AddWithValue("GlyphId", glyphId);
                sqlCommand.Parameters.AddWithValue("ItemId", itemId);
                sqlCommand.Parameters.AddWithValue("Icon", icon);
                sqlCommand.Parameters.AddWithValue("Name", name);
                sqlCommand.ExecuteScalar();
            }
        }

        public static void InsertTalents(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));
            var characterId = (string) jsonParser.SelectToken("characterId");
            int numberOfTalents = int.Parse(jsonParser.SelectToken("numberOfTalents").ToString());

            DeleteTalents(characterId, sqlConnection);

            for (int i = 0; i < numberOfTalents; i++)
            {
                var build = (string) jsonParser.SelectToken("talents[" + i + "].build");
                var icon = (string) jsonParser.SelectToken("talents[" + i + "].icon");
                var name = (string) jsonParser.SelectToken("talents[" + i + "].name");
                var selected = (string) jsonParser.SelectToken("talents[" + i + "].selected");
                var primeGlyph = (string) jsonParser.SelectToken("talents[" + i + "].primeGlyph");
                var majorGlyph = (string) jsonParser.SelectToken("talents[" + i + "].majorGlyph");
                var minorGlyph = (string) jsonParser.SelectToken("talents[" + i + "].minorGlyph");

                var sqlCommand = new SqlCommand {Connection = sqlConnection, CommandType = CommandType.StoredProcedure, CommandText = "InsertCharacterTalent"};
                sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
                sqlCommand.Parameters.AddWithValue("Build", build);
                sqlCommand.Parameters.AddWithValue("Icon", icon);
                sqlCommand.Parameters.AddWithValue("Name", name);
                sqlCommand.Parameters.AddWithValue("Selected", selected);
                sqlCommand.Parameters.AddWithValue("PrimeGlyph", primeGlyph);
                sqlCommand.Parameters.AddWithValue("MajorGlyph", majorGlyph);
                sqlCommand.Parameters.AddWithValue("MinorGlyph", minorGlyph);
                sqlCommand.ExecuteScalar();
            }
        }

        public static void InsertTitles(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            var characterId = (string) jsonParser.SelectToken("characterId");
            int numberOfTitles = int.Parse(jsonParser.SelectToken("numberOfTitles").ToString());

            for (int i = 0; i < numberOfTitles; i++)
            {
                var titleId = (string) jsonParser.SelectToken("titles[" + i + "].titleId");
                var name = (string) jsonParser.SelectToken("titles[" + i + "].name");
                var selected = (string) jsonParser.SelectToken("titles[" + i + "].selected");

                var sqlCommand = new SqlCommand {Connection = sqlConnection, CommandType = CommandType.StoredProcedure, CommandText = "InsertTitle"};
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandTimeout = 60;
                sqlCommand.Parameters.AddWithValue("TitleId", titleId);
                sqlCommand.Parameters.AddWithValue("Name", name);
                sqlCommand.ExecuteScalar();

                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "InsertCharacter_Title";
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandTimeout = 60;
                sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
                sqlCommand.Parameters.AddWithValue("TitleId", titleId);
                sqlCommand.Parameters.AddWithValue("Selected", selected);
                sqlCommand.ExecuteScalar();
            }
        }

        public static string InsertGuild(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            var guildName = (string) jsonParser.SelectToken("guildName");
            var realmId = (string) jsonParser.SelectToken("realmId");
            var level = (string) jsonParser.SelectToken("level");
            var members = (string) jsonParser.SelectToken("members");
            var side = (string) jsonParser.SelectToken("side");
            var achievementPoints = (string) jsonParser.SelectToken("achievementPoints");

            var sqlCommand = new SqlCommand {Connection = sqlConnection, CommandType = CommandType.StoredProcedure, CommandText = "InsertGuild"};
            sqlCommand.Parameters.Clear();
            sqlCommand.CommandTimeout = 60;
            sqlCommand.Parameters.AddWithValue("Name", guildName);
            sqlCommand.Parameters.AddWithValue("RealmId", realmId);
            sqlCommand.Parameters.AddWithValue("Level", level);
            sqlCommand.Parameters.AddWithValue("Members", members);
            sqlCommand.Parameters.AddWithValue("Side", side);
            sqlCommand.Parameters.AddWithValue("AchievementPoints", achievementPoints);
            string guildId = sqlCommand.ExecuteScalar().ToString();

            return guildId;
        }

        public static void InsertCharacter(string jsonQuery, SqlConnection sqlConnection)
        {
            JObject jsonParser = JObject.Parse(Misc.Decompress(jsonQuery));

            var characterName = (string) jsonParser.SelectToken("name");
            int level = int.Parse(jsonParser.SelectToken("level").ToString());
            int classId = int.Parse(jsonParser.SelectToken("class").ToString());
            int race = int.Parse(jsonParser.SelectToken("race").ToString());
            var gender = (string) jsonParser.SelectToken("gender");
            int achievementPoints = int.Parse(jsonParser.SelectToken("achievementPoints").ToString());
            var thumbnail = (string) jsonParser.SelectToken("thumbnail");
            int totalHonorableKills = int.Parse(jsonParser.SelectToken("totalHonorableKills").ToString());
            int personalBattlegroundRating = int.Parse(jsonParser.SelectToken("personalBattlegroundRating").ToString());
            int guildId = int.Parse(jsonParser.SelectToken("guildId").ToString());
            int realmId = int.Parse(jsonParser.SelectToken("realmId").ToString());
            int sideId = int.Parse(jsonParser.SelectToken("sideId").ToString());
            var lastModified = (string) jsonParser.SelectToken("lastModified");

            var sqlCommand = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection, CommandText = "InsertCharacter", CommandTimeout = 120};
            sqlCommand.Parameters.Clear();
            sqlCommand.CommandTimeout = 60;
            sqlCommand.Parameters.AddWithValue("Name", characterName);
            sqlCommand.Parameters.AddWithValue("Level", level);
            sqlCommand.Parameters.AddWithValue("Class", classId);
            sqlCommand.Parameters.AddWithValue("Race", race);
            sqlCommand.Parameters.AddWithValue("Gender", gender);
            sqlCommand.Parameters.AddWithValue("AchievementPoints", achievementPoints);
            sqlCommand.Parameters.AddWithValue("Thumbnail", thumbnail);
            sqlCommand.Parameters.AddWithValue("TotalHonorableKills", totalHonorableKills);
            sqlCommand.Parameters.AddWithValue("PersonalBattlegroundRating", personalBattlegroundRating);
            if (guildId == 0)
            {
                sqlCommand.Parameters.AddWithValue("GuildId", DBNull.Value);
            }
            else
            {
                sqlCommand.Parameters.AddWithValue("GuildId", guildId);
            }
            sqlCommand.Parameters.AddWithValue("RealmId", realmId);
            sqlCommand.Parameters.AddWithValue("SideId", sideId);
            sqlCommand.Parameters.AddWithValue("LastModified", lastModified);
            sqlCommand.ExecuteScalar();
        }

        private static void DeleteTalents(string characterId, SqlConnection sqlConnection)
        {
            var sqlDeleteCommand = new SqlCommand {Connection = sqlConnection, CommandType = CommandType.Text, CommandText = "DELETE FROM CharacterTalent WHERE CharacterId='" + characterId + "'"};
            sqlDeleteCommand.ExecuteNonQuery();
        }

        private static void InsertItem(JObject jsonParser, string itemSlot, SqlConnection sqlConnection)
        {
            if (jsonParser.SelectToken(itemSlot + ".itemId") != null)
            {
                int itemId = int.Parse(jsonParser.SelectToken(itemSlot + ".itemId").ToString());
                var icon = (string) jsonParser.SelectToken(itemSlot + ".icon");
                var name = (string) jsonParser.SelectToken(itemSlot + ".name");
                var quality = (string) jsonParser.SelectToken(itemSlot + ".quality");

                var sqlCommand = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection, CommandText = "InsertItem"};
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandTimeout = 60;
                sqlCommand.Parameters.AddWithValue("ItemId", itemId);
                sqlCommand.Parameters.AddWithValue("Icon", icon);
                sqlCommand.Parameters.AddWithValue("Name", name);
                sqlCommand.Parameters.AddWithValue("Quality", quality);
                sqlCommand.ExecuteScalar();
            }
        }

        private static void InsertItemEnhancement(string characterId, JObject jsonParser, string itemSlot, SqlConnection sqlConnection)
        {
            if (jsonParser.SelectToken(itemSlot + ".itemId") != null)
            {
                var itemId = (string) jsonParser.SelectToken(itemSlot + ".itemId");
                var enchant = (string) jsonParser.SelectToken(itemSlot + ".enchant");
                var extraSocket = (string) jsonParser.SelectToken(itemSlot + ".extraSocket");
                var gem0 = (string) jsonParser.SelectToken(itemSlot + ".gem0");
                var gem1 = (string) jsonParser.SelectToken(itemSlot + ".gem1");
                var gem2 = (string) jsonParser.SelectToken(itemSlot + ".gem2");
                var reforge = (string) jsonParser.SelectToken(itemSlot + ".reforge");
                var seed = (string) jsonParser.SelectToken(itemSlot + ".seed");
                var set = (string) jsonParser.SelectToken(itemSlot + ".set");
                var suffix = (string) jsonParser.SelectToken(itemSlot + ".suffix");

                var sqlCommand = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection, CommandText = "InsertCharacter_ItemEnhancement"};
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandTimeout = 60;
                sqlCommand.Parameters.AddWithValue("ItemId", itemId);
                sqlCommand.Parameters.AddWithValue("CharacterId", characterId);
                sqlCommand.Parameters.AddWithValue("Enchant", enchant);
                sqlCommand.Parameters.AddWithValue("ExtraSocket", extraSocket);
                sqlCommand.Parameters.AddWithValue("Gem0", gem0);
                sqlCommand.Parameters.AddWithValue("Gem1", gem1);
                sqlCommand.Parameters.AddWithValue("Gem2", gem2);
                sqlCommand.Parameters.AddWithValue("Reforge", reforge);
                sqlCommand.Parameters.AddWithValue("Seed", seed);
                sqlCommand.Parameters.AddWithValue("Set", set);
                sqlCommand.Parameters.AddWithValue("Suffix", suffix);
                sqlCommand.ExecuteScalar();
            }
        }
    }
}